package com.example.qxfw.rcyw.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.qxfw.backgroundManagement.easyExcel.entity.StationExcel;
import com.example.qxfw.common.entity.qx.SmallScaleStation;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
@DS("qx")
public interface SmallScaleStationMapper extends BaseMapper<SmallScaleStation> {
    /**
     * 查询区域自动站点
     * @return
     */
    @Select("select StationID, StationName, Province, City, County, Longitude, Latitude, Altitude, StationModel from smallscale_station")
    List<SmallScaleStation> selectList1();

    @Select("select distinct stationname from smallscale_station ")
    List<String> selectStationName();

    @Select("select distinct stationname from smallscale_station where county like '${county}%' ")
    List<String> selectStationNameByCounty(String county);

    @Select("select distinct stationName from smallscale_station where city like '${city}%'")
    List<String> selectStationNameByCity(String city);

    @Select("select distinct county from smallscale_station")
    List<String> selectCountry();

    @Select("select county from smallscale_station " +
            "where StationID='${stationId}' ")
    List<String> selectCountryByStationId(String stationId);


    @Select("select stationId from smallscale_station where stationname = #{stationName} ")
    List<String> selectStationId(String stationName);

    @Select("select stationid, stationname, county from smallscale_station order by county, stationid ")
    List<SmallScaleStation> selectList2();

    @Select("select distinct stationname from smallscale_station where City like '${city}%' order by stationname")
    List<String> selectStationNameCity(String city);

    @Select("select Distinct StationID, stationname, Longitude, Latitude " +
            "from smallscale_station " +
            "where  stationname='${stationName}'")
    List<SmallScaleStation> selectList3(String stationName);

    @Select("select Distinct StationID, stationname, Longitude, Latitude " +
            "from smallscale_station ")
    List<SmallScaleStation> selectList4();

    @Select("select stationId,stationName from smallscale_station where city like '${city}%' ")
    List<SmallScaleStation> selectList5(String city);

    /**
     * 根据市县和站点id查询站点名字和id
     *
     * @param stationid 站点id
     * @param city      城市
     * @param county    县
     * @return
     */
    @Select("SELECT DISTINCT stationname, stationid " +
            "FROM smallscale_station " +
            "WHERE stationid LIKE concat('%',#{stationid},'%') " +
            "AND city LIKE concat('%',#{city},'%') " +
            "AND county != #{county} " +
            "ORDER BY stationid ")
    List<SmallScaleStation> selectByStationIdAndCityAndNotCounty(@Param("stationid") String stationid, @Param("city") String city, @Param("county") String county);

    /**
     * 获取所有国家站
     * @return
     */
    @Select("SELECT DISTINCT stationname, stationid, Longitude, Latitude " +
            "FROM smallscale_station " +
            "WHERE stationid LIKE concat(5,'%') ")
    List<SmallScaleStation> getAllCountryStation();


    @Insert("INSERT INTO smallscale_station(stationid, stationname, province, city, county, longitude, latitude, altitude, stationmodel," +
            "observeelement,drainagearea,tributaries,starttime,jb,simcard,reservoirid,isoutput,status,town) " +
            "VALUES(#{stationid}, #{stationname}, #{province}, #{city}, #{county}, #{longitude}, #{latitude}, #{altitude}, #{stationmodel}," +
            "#{observeelement}, #{drainagearea}, #{tributaries}, #{starttime}, #{jb}, #{simcard}, #{reservoirid}, #{isoutput}, #{status}, #{town})")
    boolean insert(StationExcel user);
}
